{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL Processes\n",
    "Use this notebook to develop the ETL process for each of your tables before completing the `etl.py` file to load the whole datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "from sql_queries import *\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(\"host=127.0.0.1 dbname=sparkifydb user=student password=student\")\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_files(filepath):\n",
    "    all_files = []\n",
    "    for root, dirs, files in os.walk(filepath):\n",
    "        files = glob.glob(os.path.join(root,'*.json'))\n",
    "        for f in files :\n",
    "            all_files.append(os.path.abspath(f))\n",
    "    \n",
    "    return all_files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process `song_data`\n",
    "In this first part, you'll perform ETL on the first dataset, `song_data`, to create the `songs` and `artists` dimensional tables.\n",
    "\n",
    "Let's perform ETL on a single song file and load a single record into each table to start.\n",
    "- Use the `get_files` function provided above to get a list of all song JSON files in `data/song_data`\n",
    "- Select the first song in this list\n",
    "- Read the song file and view the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "71"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_files = get_files(\"data/song_data/\")\n",
    "len(song_files)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "filepath = song_files[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>artist_id</th>\n",
       "      <th>artist_latitude</th>\n",
       "      <th>artist_location</th>\n",
       "      <th>artist_longitude</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>num_songs</th>\n",
       "      <th>song_id</th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ARNF6401187FB57032</td>\n",
       "      <td>40.79086</td>\n",
       "      <td>New York, NY [Manhattan]</td>\n",
       "      <td>-73.96644</td>\n",
       "      <td>Sophie B. Hawkins</td>\n",
       "      <td>305.162</td>\n",
       "      <td>1</td>\n",
       "      <td>SONWXQJ12A8C134D94</td>\n",
       "      <td>The Ballad Of Sleeping Beauty</td>\n",
       "      <td>1994</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            artist_id  artist_latitude           artist_location  \\\n",
       "0  ARNF6401187FB57032         40.79086  New York, NY [Manhattan]   \n",
       "\n",
       "   artist_longitude        artist_name  duration  num_songs  \\\n",
       "0         -73.96644  Sophie B. Hawkins   305.162          1   \n",
       "\n",
       "              song_id                          title  year  \n",
       "0  SONWXQJ12A8C134D94  The Ballad Of Sleeping Beauty  1994  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(filepath, lines=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #1: `songs` Table\n",
    "#### Extract Data for Songs Table\n",
    "- Select columns for song ID, title, artist ID, year, and duration\n",
    "- Use `df.values` to select just the values from the dataframe\n",
    "- Index to select the first (only) record in the dataframe\n",
    "- Convert the array to a list and set it to `song_data`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ARNF6401187FB57032',\n",
       " 40.79086,\n",
       " 'New York, NY [Manhattan]',\n",
       " -73.96644,\n",
       " 'Sophie B. Hawkins',\n",
       " 305.162,\n",
       " 1,\n",
       " 'SONWXQJ12A8C134D94',\n",
       " 'The Ballad Of Sleeping Beauty',\n",
       " 1994]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.values[0].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "song_data = df[['song_id', 'title','artist_id', 'year', 'duration']].values[0].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('SONWXQJ12A8C134D94',\n",
       " 'The Ballad Of Sleeping Beauty',\n",
       " 'ARNF6401187FB57032',\n",
       " 1994,\n",
       " 305.162)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_data = (song_data[0], song_data[1], song_data[2], song_data[3], song_data[4])\n",
    "song_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Insert Record into Song Table\n",
    "Implement the `song_table_insert` query in `sql_queries.py` and run the cell below to insert a record for this song into the `songs` table. Remember to run `create_tables.py` before running the cell below to ensure you've created/resetted the `songs` table in the sparkify database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'INSERT INTO songs(song_id, title, artist_id,year,duration)\\n                        VALUES(%s, %s, %s, %s, %s)\\n                        ON CONFLICT (song_id) \\n                        DO NOTHING'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_table_insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "cur.execute(song_table_insert, song_data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run `test.ipynb` to see if you've successfully added a record to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #2: `artists` Table\n",
    "#### Extract Data for Artists Table\n",
    "- Select columns for artist ID, name, location, latitude, and longitude\n",
    "- Use `df.values` to select just the values from the dataframe\n",
    "- Index to select the first (only) record in the dataframe\n",
    "- Convert the array to a list and set it to `artist_data`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "artist_data = df[['artist_id', 'artist_name', 'artist_location', 'artist_latitude', 'artist_longitude']].values[0].tolist()\n",
    "artist_data = (artist_data[0], artist_data[1], artist_data[2], artist_data[3], artist_data[4])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Insert Record into Artist Table\n",
    "Implement the `artist_table_insert` query in `sql_queries.py` and run the cell below to insert a record for this song's artist into the `artists` table. Remember to run `create_tables.py` before running the cell below to ensure you've created/resetted the `artists` table in the sparkify database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'INSERT INTO  artists(artist_id, name,location,lattitude, longitude)\\n                          VALUES(%s, %s, %s, %s, %s)\\n                          ON CONFLICT (artist_id) \\n                          DO NOTHING'"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "artist_table_insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(artist_table_insert, artist_data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run `test.ipynb` to see if you've successfully added a record to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process `log_data`\n",
    "In this part, you'll perform ETL on the second dataset, `log_data`, to create the `time` and `users` dimensional tables, as well as the `songplays` fact table.\n",
    "\n",
    "Let's perform ETL on a single log file and load a single record into each table.\n",
    "- Use the `get_files` function provided above to get a list of all log JSON files in `data/log_data`\n",
    "- Select the first log file in this list\n",
    "- Read the log file and view the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "log_files = get_files(\"data/log_data/\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/home/workspace/data/log_data/2018/11/2018-11-09-events.json'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filepath = log_files[0]\n",
    "filepath"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>artist</th>\n",
       "      <th>auth</th>\n",
       "      <th>firstName</th>\n",
       "      <th>gender</th>\n",
       "      <th>itemInSession</th>\n",
       "      <th>lastName</th>\n",
       "      <th>length</th>\n",
       "      <th>level</th>\n",
       "      <th>location</th>\n",
       "      <th>method</th>\n",
       "      <th>page</th>\n",
       "      <th>registration</th>\n",
       "      <th>sessionId</th>\n",
       "      <th>song</th>\n",
       "      <th>status</th>\n",
       "      <th>ts</th>\n",
       "      <th>userAgent</th>\n",
       "      <th>userId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Muse</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>209.50159</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Supermassive Black Hole (Twilight Soundtrack V...</td>\n",
       "      <td>200</td>\n",
       "      <td>1541721977796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Beastie Boys</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>161.56689</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Lighten Up</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722186796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Shakira</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>3</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>145.84118</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Pienso En Ti</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722347796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Selena</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>4</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>172.66893</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Amor Prohibido</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722492796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kid Cudi Vs Crookers</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>5</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>162.97751</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Day 'N' Nite</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722664796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 artist       auth firstName gender  itemInSession lastName  \\\n",
       "0                  Muse  Logged In    Harper      M              1  Barrett   \n",
       "1          Beastie Boys  Logged In    Harper      M              2  Barrett   \n",
       "2               Shakira  Logged In    Harper      M              3  Barrett   \n",
       "3                Selena  Logged In    Harper      M              4  Barrett   \n",
       "4  Kid Cudi Vs Crookers  Logged In    Harper      M              5  Barrett   \n",
       "\n",
       "      length level                               location method      page  \\\n",
       "0  209.50159  paid  New York-Newark-Jersey City, NY-NJ-PA    PUT  NextSong   \n",
       "1  161.56689  paid  New York-Newark-Jersey City, NY-NJ-PA    PUT  NextSong   \n",
       "2  145.84118  paid  New York-Newark-Jersey City, NY-NJ-PA    PUT  NextSong   \n",
       "3  172.66893  paid  New York-Newark-Jersey City, NY-NJ-PA    PUT  NextSong   \n",
       "4  162.97751  paid  New York-Newark-Jersey City, NY-NJ-PA    PUT  NextSong   \n",
       "\n",
       "   registration  sessionId                                               song  \\\n",
       "0  1.540685e+12        275  Supermassive Black Hole (Twilight Soundtrack V...   \n",
       "1  1.540685e+12        275                                         Lighten Up   \n",
       "2  1.540685e+12        275                                       Pienso En Ti   \n",
       "3  1.540685e+12        275                                     Amor Prohibido   \n",
       "4  1.540685e+12        275                                       Day 'N' Nite   \n",
       "\n",
       "   status             ts                                          userAgent  \\\n",
       "0     200  1541721977796  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...   \n",
       "1     200  1541722186796  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...   \n",
       "2     200  1541722347796  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...   \n",
       "3     200  1541722492796  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...   \n",
       "4     200  1541722664796  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...   \n",
       "\n",
       "  userId  \n",
       "0     42  \n",
       "1     42  \n",
       "2     42  \n",
       "3     42  \n",
       "4     42  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(filepath, lines=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #3: `time` Table\n",
    "#### Extract Data for Time Table\n",
    "- Filter records by `NextSong` action\n",
    "- Convert the `ts` timestamp column to datetime\n",
    "  - Hint: the current timestamp is in milliseconds\n",
    "- Extract the timestamp, hour, day, week of year, month, year, and weekday from the `ts` column and set `time_data` to a list containing these values in order\n",
    "  - Hint: use pandas' [`dt` attribute](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.dt.html) to access easily datetimelike properties.\n",
    "- Specify labels for these columns and set to `column_labels`\n",
    "- Create a dataframe, `time_df,` containing the time data for this file by combining `column_labels` and `time_data` into a dictionary and converting this into a dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>artist</th>\n",
       "      <th>auth</th>\n",
       "      <th>firstName</th>\n",
       "      <th>gender</th>\n",
       "      <th>itemInSession</th>\n",
       "      <th>lastName</th>\n",
       "      <th>length</th>\n",
       "      <th>level</th>\n",
       "      <th>location</th>\n",
       "      <th>method</th>\n",
       "      <th>page</th>\n",
       "      <th>registration</th>\n",
       "      <th>sessionId</th>\n",
       "      <th>song</th>\n",
       "      <th>status</th>\n",
       "      <th>ts</th>\n",
       "      <th>userAgent</th>\n",
       "      <th>userId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Muse</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>209.50159</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Supermassive Black Hole (Twilight Soundtrack V...</td>\n",
       "      <td>200</td>\n",
       "      <td>1541721977796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Beastie Boys</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>161.56689</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Lighten Up</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722186796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Shakira</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>3</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>145.84118</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Pienso En Ti</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722347796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Selena</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>4</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>172.66893</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Amor Prohibido</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722492796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Kid Cudi Vs Crookers</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>5</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>162.97751</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>PUT</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Day 'N' Nite</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722664796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                artist       auth firstName gender  itemInSession  \\\n",
       "0      0                  Muse  Logged In    Harper      M              1   \n",
       "1      1          Beastie Boys  Logged In    Harper      M              2   \n",
       "2      2               Shakira  Logged In    Harper      M              3   \n",
       "3      3                Selena  Logged In    Harper      M              4   \n",
       "4      4  Kid Cudi Vs Crookers  Logged In    Harper      M              5   \n",
       "\n",
       "  lastName     length level                               location method  \\\n",
       "0  Barrett  209.50159  paid  New York-Newark-Jersey City, NY-NJ-PA    PUT   \n",
       "1  Barrett  161.56689  paid  New York-Newark-Jersey City, NY-NJ-PA    PUT   \n",
       "2  Barrett  145.84118  paid  New York-Newark-Jersey City, NY-NJ-PA    PUT   \n",
       "3  Barrett  172.66893  paid  New York-Newark-Jersey City, NY-NJ-PA    PUT   \n",
       "4  Barrett  162.97751  paid  New York-Newark-Jersey City, NY-NJ-PA    PUT   \n",
       "\n",
       "       page  registration  sessionId  \\\n",
       "0  NextSong  1.540685e+12        275   \n",
       "1  NextSong  1.540685e+12        275   \n",
       "2  NextSong  1.540685e+12        275   \n",
       "3  NextSong  1.540685e+12        275   \n",
       "4  NextSong  1.540685e+12        275   \n",
       "\n",
       "                                                song  status             ts  \\\n",
       "0  Supermassive Black Hole (Twilight Soundtrack V...     200  1541721977796   \n",
       "1                                         Lighten Up     200  1541722186796   \n",
       "2                                       Pienso En Ti     200  1541722347796   \n",
       "3                                     Amor Prohibido     200  1541722492796   \n",
       "4                                       Day 'N' Nite     200  1541722664796   \n",
       "\n",
       "                                           userAgent userId  \n",
       "0  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     42  \n",
       "1  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     42  \n",
       "2  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     42  \n",
       "3  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     42  \n",
       "4  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     42  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[df['page']==\"NextSong\"].reset_index()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   2018-11-09 00:06:17.796\n",
       "1   2018-11-09 00:09:46.796\n",
       "2   2018-11-09 00:12:27.796\n",
       "3   2018-11-09 00:14:52.796\n",
       "4   2018-11-09 00:17:44.796\n",
       "Name: ts, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import datetime\n",
    "t = pd.to_datetime(df.ts, unit='ms')\n",
    "t.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>artist</th>\n",
       "      <th>auth</th>\n",
       "      <th>firstName</th>\n",
       "      <th>gender</th>\n",
       "      <th>itemInSession</th>\n",
       "      <th>lastName</th>\n",
       "      <th>length</th>\n",
       "      <th>level</th>\n",
       "      <th>location</th>\n",
       "      <th>...</th>\n",
       "      <th>page</th>\n",
       "      <th>registration</th>\n",
       "      <th>sessionId</th>\n",
       "      <th>song</th>\n",
       "      <th>status</th>\n",
       "      <th>ts</th>\n",
       "      <th>userAgent</th>\n",
       "      <th>userId</th>\n",
       "      <th>week</th>\n",
       "      <th>week_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Muse</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>209.50159</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>...</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Supermassive Black Hole (Twilight Soundtrack V...</td>\n",
       "      <td>200</td>\n",
       "      <td>1541721977796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Beastie Boys</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>161.56689</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>...</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Lighten Up</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722186796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Shakira</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>3</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>145.84118</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>...</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Pienso En Ti</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722347796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Selena</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>4</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>172.66893</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>...</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Amor Prohibido</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722492796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Kid Cudi Vs Crookers</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>5</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>162.97751</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>...</td>\n",
       "      <td>NextSong</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Day 'N' Nite</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722664796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>Friday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                artist       auth firstName gender  itemInSession  \\\n",
       "0      0                  Muse  Logged In    Harper      M              1   \n",
       "1      1          Beastie Boys  Logged In    Harper      M              2   \n",
       "2      2               Shakira  Logged In    Harper      M              3   \n",
       "3      3                Selena  Logged In    Harper      M              4   \n",
       "4      4  Kid Cudi Vs Crookers  Logged In    Harper      M              5   \n",
       "\n",
       "  lastName     length level                               location    ...     \\\n",
       "0  Barrett  209.50159  paid  New York-Newark-Jersey City, NY-NJ-PA    ...      \n",
       "1  Barrett  161.56689  paid  New York-Newark-Jersey City, NY-NJ-PA    ...      \n",
       "2  Barrett  145.84118  paid  New York-Newark-Jersey City, NY-NJ-PA    ...      \n",
       "3  Barrett  172.66893  paid  New York-Newark-Jersey City, NY-NJ-PA    ...      \n",
       "4  Barrett  162.97751  paid  New York-Newark-Jersey City, NY-NJ-PA    ...      \n",
       "\n",
       "       page  registration  sessionId  \\\n",
       "0  NextSong  1.540685e+12        275   \n",
       "1  NextSong  1.540685e+12        275   \n",
       "2  NextSong  1.540685e+12        275   \n",
       "3  NextSong  1.540685e+12        275   \n",
       "4  NextSong  1.540685e+12        275   \n",
       "\n",
       "                                                song status             ts  \\\n",
       "0  Supermassive Black Hole (Twilight Soundtrack V...    200  1541721977796   \n",
       "1                                         Lighten Up    200  1541722186796   \n",
       "2                                       Pienso En Ti    200  1541722347796   \n",
       "3                                     Amor Prohibido    200  1541722492796   \n",
       "4                                       Day 'N' Nite    200  1541722664796   \n",
       "\n",
       "                                           userAgent userId week  week_day  \n",
       "0  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     42   45    Friday  \n",
       "1  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     42   45    Friday  \n",
       "2  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     42   45    Friday  \n",
       "3  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     42   45    Friday  \n",
       "4  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...     42   45    Friday  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['week'] = t.apply(lambda x: datetime.date(x.year, x.month, x.day).isocalendar()[1])\n",
    "df['week_day'] = t.apply(lambda x: datetime.date(x.year, x.month, x.day).strftime(\"%A\"))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>artist</th>\n",
       "      <th>auth</th>\n",
       "      <th>firstName</th>\n",
       "      <th>gender</th>\n",
       "      <th>itemInSession</th>\n",
       "      <th>lastName</th>\n",
       "      <th>length</th>\n",
       "      <th>level</th>\n",
       "      <th>location</th>\n",
       "      <th>...</th>\n",
       "      <th>registration</th>\n",
       "      <th>sessionId</th>\n",
       "      <th>song</th>\n",
       "      <th>status</th>\n",
       "      <th>ts</th>\n",
       "      <th>userAgent</th>\n",
       "      <th>userId</th>\n",
       "      <th>week</th>\n",
       "      <th>week_day</th>\n",
       "      <th>start_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Muse</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>1</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>209.50159</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>...</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Supermassive Black Hole (Twilight Soundtrack V...</td>\n",
       "      <td>200</td>\n",
       "      <td>1541721977796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2018-11-09 00:06:17.796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Beastie Boys</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>2</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>161.56689</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>...</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Lighten Up</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722186796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2018-11-09 00:09:46.796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Shakira</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>3</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>145.84118</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>...</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Pienso En Ti</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722347796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2018-11-09 00:12:27.796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Selena</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>4</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>172.66893</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>...</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Amor Prohibido</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722492796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2018-11-09 00:14:52.796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Kid Cudi Vs Crookers</td>\n",
       "      <td>Logged In</td>\n",
       "      <td>Harper</td>\n",
       "      <td>M</td>\n",
       "      <td>5</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>162.97751</td>\n",
       "      <td>paid</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>...</td>\n",
       "      <td>1.540685e+12</td>\n",
       "      <td>275</td>\n",
       "      <td>Day 'N' Nite</td>\n",
       "      <td>200</td>\n",
       "      <td>1541722664796</td>\n",
       "      <td>\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...</td>\n",
       "      <td>42</td>\n",
       "      <td>45</td>\n",
       "      <td>Friday</td>\n",
       "      <td>2018-11-09 00:17:44.796</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index                artist       auth firstName gender  itemInSession  \\\n",
       "0      0                  Muse  Logged In    Harper      M              1   \n",
       "1      1          Beastie Boys  Logged In    Harper      M              2   \n",
       "2      2               Shakira  Logged In    Harper      M              3   \n",
       "3      3                Selena  Logged In    Harper      M              4   \n",
       "4      4  Kid Cudi Vs Crookers  Logged In    Harper      M              5   \n",
       "\n",
       "  lastName     length level                               location  \\\n",
       "0  Barrett  209.50159  paid  New York-Newark-Jersey City, NY-NJ-PA   \n",
       "1  Barrett  161.56689  paid  New York-Newark-Jersey City, NY-NJ-PA   \n",
       "2  Barrett  145.84118  paid  New York-Newark-Jersey City, NY-NJ-PA   \n",
       "3  Barrett  172.66893  paid  New York-Newark-Jersey City, NY-NJ-PA   \n",
       "4  Barrett  162.97751  paid  New York-Newark-Jersey City, NY-NJ-PA   \n",
       "\n",
       "            ...            registration sessionId  \\\n",
       "0           ...            1.540685e+12       275   \n",
       "1           ...            1.540685e+12       275   \n",
       "2           ...            1.540685e+12       275   \n",
       "3           ...            1.540685e+12       275   \n",
       "4           ...            1.540685e+12       275   \n",
       "\n",
       "                                                song  status             ts  \\\n",
       "0  Supermassive Black Hole (Twilight Soundtrack V...     200  1541721977796   \n",
       "1                                         Lighten Up     200  1541722186796   \n",
       "2                                       Pienso En Ti     200  1541722347796   \n",
       "3                                     Amor Prohibido     200  1541722492796   \n",
       "4                                       Day 'N' Nite     200  1541722664796   \n",
       "\n",
       "                                           userAgent  userId week week_day  \\\n",
       "0  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...      42   45   Friday   \n",
       "1  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...      42   45   Friday   \n",
       "2  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...      42   45   Friday   \n",
       "3  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...      42   45   Friday   \n",
       "4  \"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebK...      42   45   Friday   \n",
       "\n",
       "               start_time  \n",
       "0 2018-11-09 00:06:17.796  \n",
       "1 2018-11-09 00:09:46.796  \n",
       "2 2018-11-09 00:12:27.796  \n",
       "3 2018-11-09 00:14:52.796  \n",
       "4 2018-11-09 00:17:44.796  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_data = (t, t.dt.hour, t.dt.day, df.week, t.dt.month, t.dt.year, df.week_day)\n",
    "column_labels = ['start_time','hour','day','week','month', 'year','weekday']\n",
    "time_df = pd.DataFrame(dict(zip(column_labels, time_data)))\n",
    "time_df.head()\n",
    "df['start_time'] = t\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Insert Records into Time Table\n",
    "Implement the `time_table_insert` query in `sql_queries.py` and run the cell below to insert records for the timestamps in this log file into the `time` table. Remember to run `create_tables.py` before running the cell below to ensure you've created/resetted the `time` table in the sparkify database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'INSERT INTO time(start_time,hour,day,week,month, year,weekday)\\n                        VALUES(%s, %s, %s, %s, %s, %s, %s)'"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_table_insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, row in time_df.iterrows():\n",
    "    cur.execute(time_table_insert, list(row))\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run `test.ipynb` to see if you've successfully added records to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #4: `users` Table\n",
    "#### Extract Data for Users Table\n",
    "- Select columns for user ID, first name, last name, gender and level and set to `user_df`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>userId</th>\n",
       "      <th>firstName</th>\n",
       "      <th>lastName</th>\n",
       "      <th>gender</th>\n",
       "      <th>level</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>42</td>\n",
       "      <td>Harper</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>M</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>42</td>\n",
       "      <td>Harper</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>M</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>42</td>\n",
       "      <td>Harper</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>M</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>42</td>\n",
       "      <td>Harper</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>M</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>42</td>\n",
       "      <td>Harper</td>\n",
       "      <td>Barrett</td>\n",
       "      <td>M</td>\n",
       "      <td>paid</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  userId firstName lastName gender level\n",
       "0     42    Harper  Barrett      M  paid\n",
       "1     42    Harper  Barrett      M  paid\n",
       "2     42    Harper  Barrett      M  paid\n",
       "3     42    Harper  Barrett      M  paid\n",
       "4     42    Harper  Barrett      M  paid"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_df = df[['userId','firstName', 'lastName', 'gender', 'level']]\n",
    "user_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Insert Records into Users Table\n",
    "Implement the `user_table_insert` query in `sql_queries.py` and run the cell below to insert records for the users in this log file into the `users` table. Remember to run `create_tables.py` before running the cell below to ensure you've created/resetted the `users` table in the sparkify database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, row in user_df.iterrows():\n",
    "    cur.execute(user_table_insert, row)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run `test.ipynb` to see if you've successfully added records to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## #5: `songplays` Table\n",
    "#### Extract Data and Songplays Table\n",
    "This one is a little more complicated since information from the songs table, artists table, and original log file are all needed for the `songplays` table. Since the log file does not specify an ID for either the song or the artist, you'll need to get the song ID and artist ID by querying the songs and artists tables to find matches based on song title, artist name, and song duration time.\n",
    "- Implement the `song_select` query in `sql_queries.py` to find the song ID and artist ID based on the title, artist name, and duration of a song.\n",
    "- Select the timestamp, user ID, level, song ID, artist ID, session ID, location, and user agent and set to `songplay_data`\n",
    "\n",
    "#### Insert Records into Songplays Table\n",
    "- Implement the `songplay_table_insert` query and run the cell below to insert records for the songplay actions in this log file into the `songplays` table. Remember to run `create_tables.py` before running the cell below to ensure you've created/resetted the `songplays` table in the sparkify database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SELECT songs.song_id, artists.artist_id FROM songs \\n                  JOIN artists ON  songs.artist_id=artists.artist_id\\n                  WHERE songs.title=%s AND artists.name=%s AND songs.duration=%s;\\n                  '"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song_select"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'INSERT INTO songplays( start_time, user_id,level,artist_id,song_id, session_id, location, user_agent)\\n                            VALUES(%s, %s, %s, %s, %s, %s, %s, %s)'"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "songplay_table_insert"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "for index, row in df.iterrows():\n",
    "    \n",
    "    # get songid and artistid from song and artist tables\n",
    "    cur.execute(song_select, (row.song, row.artist, row.length))\n",
    "    results = cur.fetchone()\n",
    "#     print(results)\n",
    "    if results:\n",
    "        songid, artistid = results\n",
    "    else:\n",
    "        songid, artistid = None, None\n",
    "\n",
    "    # start_time, user_id, level, song_id, artist_id, session_id, location, user_agent\n",
    "    # insert songplay record\n",
    "    songplay_data = (str(row.start_time),row.userId,row.level,songid,artistid, row.sessionId,row.location,row.userAgent)\n",
    "    cur.execute(songplay_table_insert, songplay_data)\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run `test.ipynb` to see if you've successfully added records to this table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Close Connection to Sparkify Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Implement `etl.py`\n",
    "Use what you've completed in this notebook to implement `etl.py`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
